package com.oa.ylms.apps.dao.account.impl;

import java.sql.SQLException;
import java.util.Calendar;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.oa.ylms.apps.dao.account.IAccountInfoDao;
import com.oa.ylms.apps.entity.account.AccountInfoValue;

public class AccountInfoDaoImpl extends HibernateDaoSupport implements
		IAccountInfoDao {
	private List<AccountInfoValue> accountInfoList;
	private int numbers;
	@Override
	public int addAccountInfo(AccountInfoValue accountInfo) throws Exception {
		if (accountInfo != null) {
			this.getHibernateTemplate().save(accountInfo);
			return accountInfo.getAccountId();
		}
		return 0;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AccountInfoValue> queryAccountInfoWithPage(
			final AccountInfoValue accountInfo, final int rows, final int pageNo) {
		if (accountInfo != null) {
			String hqlStr = "from AccountInfoValue aiv where 1=1  ";
			if(accountInfo.getStaffInfo()!=null){
				hqlStr+=" AND aiv.staffInfo=?";
			}
			if (accountInfo.getInOrOut() != null) {
				hqlStr += " AND aiv.inOrOut = ? ";
			}
			if(accountInfo.getStates()!=null){
				if(accountInfo.getStates()==-3){
					hqlStr+=" AND aiv.states>=0 ";
				}else{
				hqlStr+=" AND aiv.states=? ";
				}
			}
			hqlStr+=" ORDER BY aiv.createTime DESC";
			final String hql = hqlStr;
			accountInfoList = this.getHibernateTemplate().executeFind(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							Query query = session.createQuery(hql);
							int index = -1;
							if(accountInfo.getStaffInfo()!=null){
							query.setEntity(++index, accountInfo.getStaffInfo());
							}
							if (accountInfo.getInOrOut() != null) {
								query.setInteger(++index, accountInfo.getInOrOut());
							}
							if(accountInfo.getStates()!=null&&accountInfo.getStates()!=-3){
							
								query.setInteger(++index, accountInfo.getStates());
							}
							query.setFirstResult(rows * (pageNo - 1));
							query.setMaxResults(rows);
							accountInfoList = query.list();
							return accountInfoList;
						}
					});
			return accountInfoList;
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public int countTpages(final AccountInfoValue accountInfo, int rows)
			throws Exception {
			if (accountInfo != null) {
				int pageNumbers = 0;
				String hqlStr = "from AccountInfoValue aiv where 1=1  ";
				if(accountInfo.getStaffInfo()!=null){
					hqlStr+=" AND aiv.staffInfo=?";
				}
				if (accountInfo.getInOrOut() != null) {
					hqlStr += " AND aiv.inOrOut = ?";
				}
				if(accountInfo.getStates()!=null){
					hqlStr+=" AND aiv.states=? ";
				}
				hqlStr+=" ORDER BY aiv.createTime DESC";
				final String hql = hqlStr;
				accountInfoList = this.getHibernateTemplate().executeFind(
						new HibernateCallback() {
							public Object doInHibernate(Session session)
									throws HibernateException, SQLException {
								Query query = session.createQuery(hql);
								int index = -1;
								if(accountInfo.getStaffInfo()!=null){
								query.setEntity(++index, accountInfo.getStaffInfo());
								}
								if (accountInfo.getInOrOut() != null) {
									query.setInteger(++index, accountInfo.getInOrOut());
								}
								if(accountInfo.getStates()!=null){
									query.setInteger(++index, accountInfo.getStates());
								}
								accountInfoList = query.list();
								return accountInfoList;
							}
						});
				numbers = accountInfoList.size();
				if (numbers <= rows) {
					pageNumbers = 1;
				} else {
					if (numbers % rows == 0) {
						pageNumbers = numbers / rows;
					} else {
						pageNumbers = numbers / rows + 1;
					}
				}
				return pageNumbers;
			}
			return 0;
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<AccountInfoValue> queryAccountCheck(
			final AccountInfoValue accountInfo) {
		if (accountInfo != null) {
			String hqlStr="from AccountInfoValue aiv where 1=1 and aiv.states=?";
			if (accountInfo.getInOrOut() != null) {
				hqlStr+=" AND aiv.inOrOut=? ";
			}
			hqlStr+=" order by aiv.createTime DESC";
			final String hql=hqlStr;
			accountInfoList = this.getHibernateTemplate().executeFind(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							Query query = session.createQuery(hql);
							query.setInteger(0, accountInfo.getStates());
							if(accountInfo.getInOrOut()!=null){
							query.setInteger(1, accountInfo.getInOrOut());
							}
							accountInfoList = query.list();
							return accountInfoList;
						}
					});
			return accountInfoList;
		}
		return null;
	}

	@Override
	public AccountInfoValue queryById(AccountInfoValue accountInfo)
			throws Exception {
		if (accountInfo != null) {
			return (AccountInfoValue) this.getHibernateTemplate().get(
					AccountInfoValue.class, accountInfo.getAccountId());
		}
		return null;
	}


	@Override
	public boolean modifyAccountInfo(AccountInfoValue accountInfo)
			throws Exception {
		if (accountInfo != null) {
			try {
				this.getHibernateTemplate().update(accountInfo);
			} catch (Exception e) {
				return false;
			}
			return true;
		}
		return false;

	}


	@SuppressWarnings("unchecked")
	@Override
	public List<AccountInfoValue> queryAccountInfo(String inOrOut,
			String startDate, String endDate, String title, String posters,
			String state) {
		String hql = "SELECT aiv.* from AccountInfoValue aiv WHERE 1=1 ";
		if (inOrOut != "")
			hql += (" AND aiv.inOrOut=" + inOrOut);
		if (title != "")
			hql += (" AND aiv.title like '" + title + "%' ");
		if (posters != "")
			hql += (" AND aiv.staffId IN (" + posters + ")");
		if (state != "")
			hql += (" AND aiv.states=" + state);

		String dateTimePart = getDateQueryString("aiv.createTime", startDate,
				endDate);

		if (dateTimePart != "")
			hql = hql + " AND " + dateTimePart;
		// return hql;
		return this.getHibernateTemplate().find(hql);
	}

	private static String getDateQueryString(String columnName,
			String startDate, String endDate) {
		if(startDate.length()==0 && endDate.length()==0)
			return "";
		String[] startDateParts = startDate.split("-");
		String[] endDateParts = endDate.split("-");

		String startYear = "", startMonth = "", startDay = "", startResult = "";
		String endYear = "", endMonth = "", endDay = "", endResult = "";

		Calendar c = Calendar.getInstance();
		int year = c.get(Calendar.YEAR);
		int month = c.get(Calendar.MONTH);
		int day = c.get(Calendar.DAY_OF_MONTH);

		String result = "";
		// query Singly by startDate
		if (startDateParts != null) {
			if (startDateParts.length >= 0 && startDateParts[0] != "") {
				startYear = startDateParts[0];
				startResult += (getDatePart("YEAR", columnName) + "=" + startYear);
			} else
				startYear = String.valueOf(year);
			if (startDateParts.length > 1 && startDateParts[1] != "") {
				startMonth = startDateParts[1];
				startResult += (" AND " + getDatePart("MONTH", columnName)
						+ "=" + startMonth);
			} else
				startMonth = String.valueOf(month);
			if (startDateParts.length > 2 && startDateParts[2] != "") {
				startDay = startDateParts[2];
				startResult += (" AND " + getDatePart("DAY", columnName) + "=" + startDay);
			} else
				startDay = String.valueOf(day);
		}
		if (endDateParts != null && endDateParts.length > 0) {
			if (endDateParts.length > 0 && endDateParts[0] != "")
				endYear = endDateParts[0];
			if (endDateParts.length > 1 && endDateParts[1] != "")
				endMonth = endDateParts[1];
			else
				endMonth = startMonth;
			if (endDateParts.length > 2 && endDateParts[2] != "")
				endDay = endDateParts[2];
			else
				endDay = startDay;
			startResult = String.format("{0}-{1}-{2}", startYear, startMonth,
					startDay);
			endResult = String.format("{0}-{1}-{2}", endYear, endMonth, endDay);
			result = String.format(" '{0}' BETWEEN '{1}' AND '{2}' ", columnName,
					startResult, endResult);
		} else {
			result = startResult;
		}
		return result;
	}

	private static String getDatePart(String part, String columnName) {
		return String.format(" {0}({1})", part, columnName);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AccountInfoValue> queryAccountInfo1(
			AccountInfoValue accountInfo, String startDate, String endDate,
			String staffIds) {
		String hql = "from AccountInfoValue aiv WHERE 1=1 ";
		if (accountInfo.getInOrOut() != null)
			hql += (" AND aiv.inOrOut=" + accountInfo.getInOrOut());
		if (accountInfo.getTitle() != "" && accountInfo.getTitle() != null)
			hql += (" AND aiv.title like '" + accountInfo.getTitle() + "%' ");
		if (staffIds != "")
			hql += (" AND aiv.staffId IN (" + staffIds + ")");
		if (accountInfo.getStates() != -1)
			hql += (" AND aiv.states=" + accountInfo.getStates());

		String dateTimePart = getDateQueryString("aiv.createTime", startDate,
				endDate);

		if (dateTimePart != "")
			hql = hql + " AND " + dateTimePart;
		// return hql;
		return this.getHibernateTemplate().find(hql);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<AccountInfoValue> queryAccountInfo(final Integer year, final Integer month,
			final Integer inOrOut) throws Exception {
			String hqlStr = "from AccountInfoValue aiv where 1=1  ";
			if(year !=null){
				hqlStr+=" AND year(aiv.createTime) = ?";
			}
			if (month != null) {
				hqlStr += " AND month(aiv.createTime) = ?";
			}
			if(inOrOut!=null){
				hqlStr+=" AND aiv.inOrOut = ? ";
			}
			hqlStr+=" AND aiv.states = -2 ORDER BY aiv.createTime";
			final String hql = hqlStr;
			accountInfoList = this.getHibernateTemplate().executeFind(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							Query query = session.createQuery(hql);
							int index = -1;
							if(year != null){
								query.setInteger(++index, year);
							}
							if(month != null) {
								query.setInteger(++index, month);
							}
							if(inOrOut!=null){
								query.setInteger(++index, inOrOut);
							}
							accountInfoList = query.list();
							return accountInfoList;
						}
					});
		   return accountInfoList;
	}
	
	
	public List<AccountInfoValue> getAccountInfoList() {
		return accountInfoList;
	}

	public void setAccountInfoList(List<AccountInfoValue> accountInfoList) {
		this.accountInfoList = accountInfoList;
	}

	public int getNumbers() {
		return numbers;
	}

	public void setNumbers(int numbers) {
		this.numbers = numbers;
	}
	public int getTotalNumber(){
		return numbers;
	}
}
